
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class MACDdata {
    public static void main(String[] args)
    {
        try
        {
            String sym = "tcsbo";
            FileInputStream file = new FileInputStream(new File("D:/project/src/java/quotes/"+sym+".xls"));
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            int len = sheet.getLastRowNum();

            sheet.getRow(0).createCell(7).setCellValue("SMA 12 day");
            sheet.getRow(0).createCell(8).setCellValue("EMA 12 day");
            sheet.getRow(0).createCell(9).setCellValue("SMA 26 day");
            sheet.getRow(0).createCell(10).setCellValue("EMA 26 day");
            sheet.getRow(0).createCell(11).setCellValue("MACD line");
            
            sheet.getRow(0).createCell(12).setCellValue("Signal SMA");
            sheet.getRow(0).createCell(13).setCellValue("Signal EMA");
            sheet.getRow(0).createCell(14).setCellValue("Histogram");
//for SMA 
            for(int i=(len-10);i>1;i--)
            {
                sheet.getRow(i-1).createCell(7).setCellFormula("AVERAGE(E"+(i)+":E"+(i+11)+")");
            }
            
            for(int i=(len-24);i>1;i--)
            {
                sheet.getRow(i-1).createCell(9).setCellFormula("AVERAGE(E"+(i)+":E"+(i+25)+")");
            }

//for EMA             
            sheet.getRow((len-11)).createCell(8).setCellFormula("H"+(len-10));
            sheet.getRow((len-25)).createCell(10).setCellFormula("J"+(len-24));
                        
            for(int i=(len-11);i>1;i--)
            {
                sheet.getRow(i-1).createCell(8).setCellFormula("0.1538*(E"+(i)+"-I"+(i+1)+")+I"+(i+1));
            }
            
            for(int i=(len-25);i>1;i--)
            {
                sheet.getRow(i-1).createCell(10).setCellFormula("0.07407*(E"+(i)+"-J"+(i+1)+")+J"+(i+1));
            }
            
// for MACD line
            for(int i=(len-24);i>1;i--)
            {
                sheet.getRow(i-1).createCell(11).setCellFormula("I"+(i)+"-K"+(i));
            }
            
//            sheet.getRow(len-33).createCell(12).setCellFormula("AVERAGE(L"+(len-32)+":L"+(len-24)+")");
            
//for signal
            for(int i=(len-32);i>1;i--)
            {
                sheet.getRow(i-1).createCell(12).setCellFormula("AVERAGE(L"+(i)+":L"+(i+8)+")");
            }
            
            sheet.getRow(len-33).createCell(13).setCellFormula("M"+(len-32));
            
            for(int i=(len-33);i>1;i--)
            {
                sheet.getRow(i-1).createCell(13).setCellFormula("0.2*(E"+(i)+"-N"+(i+1)+")+N"+(i+1));
            }

//for histogram
            
            for(int i=(len-32);i>1;i--)
            {
                sheet.getRow(i-1).createCell(14).setCellFormula("L"+(i)+"-N"+(i));
            }
            
            file.close();
			
            FileOutputStream outFile =new FileOutputStream(new File("D:/project/src/java/quotes/"+sym+".xls"));
            workbook.write(outFile);
            outFile.close();
        }
        catch (FileNotFoundException e) {e.printStackTrace();} 
        catch (IOException e) {e.printStackTrace();}
    }
}
